package com.hp.attendance.dao;

import com.hp.attendance.entity.Department;
import com.hp.attendance.entity.Employee;
import com.hp.attendance.entity.History;
import com.hp.attendance.entity.Position;
import com.hp.attendance.util.BaseDAO;

import java.io.IOException;
import java.sql.SQLException;
import java.util.List;

public class HistoryBysDao extends BaseDAO {

    //-------------------------------员工总档案分页⬇---------------------------------
    /**
     * 分页查询记录
     * @param pageNo 当前页
     * @param pageSize 每页显示的条数
     * @return
     */
    public List<History> getLimitList(int pageNo, int pageSize) throws SQLException, IOException, ClassNotFoundException, InstantiationException, IllegalAccessException {
        String sql ="select * from history limit ?,?";
        Object[] obj ={(pageNo-1)*pageSize,pageSize};
        List<History> history = queryList(sql, obj, new History());
        return history;
    }

    /**
     * 查询总记录数
     * @return
     * @throws SQLException
     */
    public long getTotal() throws SQLException, IOException, ClassNotFoundException, InstantiationException, IllegalAccessException {
        String sql ="select count(1) from history";
        int count = queryCount(sql, new Object[0]);
        return count;
    }


    //-----------------------------员工总档案分页⬆---------------------------------



    /**
     * 查询员工档案（包含离休和在职的所有员工信息）
     * @return
     */
    public List<History> getHisList() throws SQLException, IOException, ClassNotFoundException, InstantiationException, IllegalAccessException {
        String sql = "select * from history";
        List<History> list = queryList(sql, new Object[0], new History());
        return list;
    }

    /**
     * 根据部门编号查询部门名称
     * @param department_number
     * @return
     */
    public Department getDepName(int department_number) throws SQLException, IOException, ClassNotFoundException, InstantiationException, IllegalAccessException {
        String sql = "select * from department where department_number=?";
        Object[] obj = {department_number};
        Department department = queryOne(sql, obj, new Department());
        return department;
    }

    /**
     * 根据职位编号查询职位名称
     * @param position_number
     * @return
     */
    public Position getPosName(int position_number) throws SQLException, IOException, ClassNotFoundException, InstantiationException, IllegalAccessException {
        String sql = "select * from position where position_number=?";
        Object[] obj = {position_number};
        Position position = queryOne(sql, obj, new Position());
        return position;
    }


    //---------------------在在职员工管理页面添加员工的同时需要在档案中添加员工数据---------------
    /**
     * 添加员工信息
     * @param history
     */
    public void AddHistory(History history) throws SQLException, IOException, ClassNotFoundException {
        String sql = "insert into history values(null,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
        Object[] obj = {history.getEmployee_number(),history.getName(),history.getGender(),history.getBirthday(),history.getTelephone(),
                history.getEmail(),history.getAddress(),history.getPhoto(),history.getEducation(),history.getIn_time(),history.getOut_time(),
                history.getDepartment_number(), history.getPosition_number(),history.getStatus(),history.getHome(),history.getNotes()};
        addOrUpdOrDel(sql,obj);
    }


    //----------------------从history表中查询状态为离职的员工信息------------------------------

    /**
     * 根据工号查询history信息
     * @param employee_number
     * @return
     */
    public History getHistoryByNum(int employee_number) throws SQLException, IOException, ClassNotFoundException, InstantiationException, IllegalAccessException {
        String sql = "select * from history where employee_number=?";
        Object[] obj = {employee_number};
        History history = queryOne(sql, obj, new History());
        return history;
    }




    //-------------------------------离职员工分页⬇---------------------------------
    /**
     * 分页查询记录
     * @param pageNo 当前页
     * @param pageSize 每页显示的条数
     * @return
     */
    public List<History> getLimitListByStatus(int pageNo, int pageSize) throws SQLException, IOException, ClassNotFoundException, InstantiationException, IllegalAccessException {
//        String sql ="select * from history limit ?,?";
//        String sql = "'select * from history where status='+ '离职' + 'limit ?,?'";
//        String a = "离职";
//        String sql = "select * from history where status = '"+ a +"' limit?,? ";
        String sql = "select * from history where status = '离职'  limit ?,? ";
        Object[] obj ={(pageNo-1)*pageSize,pageSize};
        List<History> history = queryList(sql, obj, new History());
        return history;
    }

    /**
     * 查询离休员工列表
     * @return
     * @throws SQLException
     */
    public long getTotalee() throws SQLException, IOException, ClassNotFoundException, InstantiationException, IllegalAccessException {
        String sql ="select count(1) from history where status = '离职'";
        int count = queryCount(sql, new Object[0]);
        return count;
    }
    //-----------------------------离职员工分页⬆---------------------------------
    //----------------------------修改员工信息---------------------------------

    /**
     * 修改员工信息
     * @param history
     */
    public void UpdateHistoryByNum(History history) throws SQLException, IOException, ClassNotFoundException {
        String sql = "update history set name=?,gender=?,birthday=?,telephone=?,email=?,address=?,education=?," +
                "home=?,notes=? where employee_number=?";
        Object[] obj = {history.getName(),history.getGender(),history.getBirthday(),history.getTelephone(),
                history.getEmail(),history.getAddress(),history.getEducation(),history.getHome(),history.getNotes(),history.getEmployee_number()};
        addOrUpdOrDel(sql,obj);
    }


}
